import sqlite3
from sqlite3 import Error
import configparser
import pandas as pd
import re

mongo_config = configparser.ConfigParser()
mongo_config.read('conf/mongo.ini')

project_config = configparser.ConfigParser()
project_config.read('conf/project.ini')


class MangoDatabase:
    def __init__(self):
        """Initialize the database connection."""
        self.db_file = mongo_config.get('mango_db', 'database_path')
        self.connection = None
        try:
            self.connection = sqlite3.connect(self.db_file, check_same_thread=False)
            print(f"Connected to SQLite database: {self.db_file}")
        except Error as e:
            print(e)

    def create_table(self, create_table_sql):
        """Create a table from the create_table_sql statement."""
        try:
            cursor = self.connection.cursor()
            cursor.execute(create_table_sql)
            self.connection.commit()
            print("Table created successfully")
        except Error as e:
            print(e)

    def insert_data(self, table, data):
        """Insert a row of data into the table."""
        placeholders = ', '.join(['?'] * len(data))
        sql = f'INSERT OR REPLACE INTO {table} VALUES ({placeholders})'
        try:
            cursor = self.connection.cursor()
            cursor.execute(sql, data)
            self.connection.commit()
            print(f"Data: {data} inserted successfully")
        except Error as e:
            print(e)
    
    def insert_excel(self):
        # Create a table
        sections = project_config.sections()
        for section in sections:
            create_table_sql = f'''CREATE TABLE IF NOT EXISTS {project_config.get(section, 'db_table')} (
                                    id TEXT PRIMARY KEY,
                                    skill TEXT NOT NULL,
                                    tts TEXT
                                );'''

            self.create_table(create_table_sql)

            # 读取Excel文件
            xls = pd.ExcelFile(project_config.get(section, 'tts'), engine='openpyxl')
            for sheet_name in xls.sheet_names:
                df = pd.read_excel(xls, sheet_name=sheet_name)
                # 遍历每一行数据
                for index, row in df.iterrows():
                    # 提取序号、sheet名和TTS文言
                    serial_id = row['序号']
                    tts = re.sub(r'【.*?】', '*', str(row['TTS']))
                    # 构造输出格式
                    output = (str(serial_id).strip(), sheet_name, str(tts).replace(' ', '').replace('\r\n', '\n').replace('\n', '|'))
                    # 打印输出（或根据需要保存到文件或进行其他处理）
                    self.insert_data(project_config.get(section, 'db_table'), output)


    def update_data(self, table, data, condition, condition_value):
        """Update a row in the table with the specified condition."""
        set_clause = ', '.join([f"{key} = ?" for key in data.keys()])
        sql = f'UPDATE {table} SET {set_clause} WHERE {condition}'
        try:
            values = tuple(data.values()) + (condition_value,)  # Assume condition_value is provided separately
            cursor = self.connection.cursor()
            cursor.execute(sql, values)
            self.connection.commit()
            print("Data updated successfully")
        except Error as e:
            print(e)

    def delete_data(self, table, condition, condition_value):
        """Delete a row from the table with the specified condition."""
        sql = f'DELETE FROM {table} WHERE {condition} = ?'
        try:
            cursor = self.connection.cursor()
            cursor.execute(sql, (condition_value,))
            self.connection.commit()
            print("Data deleted successfully")
        except Error as e:
            print(e)

    def query_data(self, table, columns='*', condition=None, condition_value=None):
        """Query data from the table with the specified condition."""
        if condition:
            sql = f'SELECT {columns} FROM {table} WHERE {condition} = ?'
            cursor = self.connection.cursor()
            cursor.execute(sql, (condition_value,))
        else:
            sql = f'SELECT {columns} FROM {table}'
            cursor = self.connection.cursor()
            cursor.execute(sql)

        rows = cursor.fetchall()
        if len(rows) == 0:
            return ""
        else:
            _, _, tts = rows[0]
            return tts

    def close_connection(self):
        """Close the database connection."""
        if self.connection:
            self.connection.close()
            print("The connection is closed")


# Example usage:
if __name__ == '__main__':
    database = MangoDatabase()
    database.insert_excel()
    # print(database.query_data(table='TTS', condition="id", condition_value="GSGMKG-09"))
    # Close connection
    database.close_connection()
